﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using INTEX.DataBase.Tools;
using ReflectionStudio.Core.Events;
namespace WPFExcelReport
{    
    public partial class JLTRWB18Data : BaseFunction
    {
        ApplicationBase app
        {
            get
            {
                return ApplicationBase.Instance;
            }
        }
        INTEXDBC INTEXDBC
        {
            get
            {
                return app.INTEXDBC;
            }
        }
        /*public INTEXBL INTEXBL
        {
            get
            {
                return app.INTEXBL;
            }
        }

        SETINTEXDB INTEXDB = new SETINTEXDB();
         */
        public JLTRWB18Data()
            : base()
        {
        }
         public JLTRWB18Data(Dictionary<string, object> DicGlobalPara)
            : base(DicGlobalPara)
        {
        }

        public DataSet GetRWB18(DataRow dwTABEL)
        {
            DataSet Result;
            DataSet Return = new DataSet();
            string TableName = "RWB18";
            string CO = dwTABEL["CO"].ToString().Trim();
            string STAFF = dwTABEL["STAFF"].ToString().Trim();
            string FROMDATE = dwTABEL["FROMDATE"].ToString().Trim();
            string ENDDATE = dwTABEL["ENDDATE"].ToString().Trim();
            try
            {
                orlReadCommand = INTEXDBC.OracleDB.CreateCommand();
                orlReadCommand.CommandText = " SELECT wbfrwlfd,wbfrwpos,wbffpos,WBBSTKNR, "
                    + " SHIFT,WBBWARSCH,WBBEINDAT,wbbwmpos,wbbwbmlfdnr,article, "
                    + " stru,DDIS,SCHL,WBFFSCHL,PCEMTR,STARTMTR,ENDMTR,mtr,SMTR,TMTR,OMTR, "
                    + " (mtr-NVL(SMTR,0)-NVL(TMTR,0)-NVL(OMTR,0)) AS FMTR, "
                    + " DECODE(MTR,0,'0%',ROUND((mtr-NVL(SMTR,0)-NVL(TMTR,0)-NVL(OMTR,0))/MTR*100,1)||'%') AS QRATE, "
                    + " ROUND(MTR*DDIS/80,1) AS QTY "
                    + " FROM ( "
                    + " SELECT WBBWJJJJ,WBBWBNR,wbfrwlfd,wbfrwpos,wbffpos,WBBSTKNR, "
                    + " SHIFT,WBBWARSCH,WBBEINDAT,wbbwmpos,wbbwbmlfdnr,article, "
                    + " (select distinct trim(substr(sddaten,52,10)) from sddat where sdsa = '0734' "
                    + " and substr(sdkey,1,3)=x.WBFMANDANT||x.stru ) as stru,DDIS, "
                    + " WBFFSCHL,SCHL,PCEMTR,startmtr*0.001 AS STARTMTR,endmtr*0.001 AS ENDMTR, "
                    + " ((case when endmtr<startmtr then startmtr else endmtr end)-startmtr)*0.001 as mtr, "
                    + " (SELECT ROUND(SUM((w.wbfbrtvon)*0.001),1) FROM WBFEHL W  "
                    + " WHERE W.WBFFSCHL ='902' AND W.WBFMANDANT = x.WBFMANDANT AND W.WBFRWKRS = x.WBFRWKRS "
                    + " AND W.WBFRWLFD = x.WBFRWLFD AND W.WBFRWPOS = x.WBFRWPOS "
                    + " AND W.WBFLGVON >x.startmtr  AND W.WBFLGBIS < x.endmtr ) AS SMTR, "
                    + " (SELECT ROUND(SUM((w.wbfbrtvon)*0.001),1) FROM WBFEHL W  "
                    + " WHERE W.WBFFSCHL ='903' AND W.WBFMANDANT = x.WBFMANDANT AND W.WBFRWKRS = x.WBFRWKRS "
                    + " AND W.WBFRWLFD = x.WBFRWLFD AND W.WBFRWPOS = x.WBFRWPOS "
                    + " AND W.WBFLGVON > x.startmtr AND W.WBFLGBIS < x.endmtr ) AS TMTR, "
                    + " (SELECT ROUND(SUM((w.wbfbrtvon)*0.001),1) FROM WBFEHL W  "
                    + " WHERE W.WBFFSCHL ='904' AND W.WBFMANDANT = x.WBFMANDANT AND W.WBFRWKRS = x.WBFRWKRS "
                    + " AND W.WBFRWLFD = x.WBFRWLFD AND W.WBFRWPOS = x.WBFRWPOS "
                    + " AND W.WBFLGVON > x.startmtr AND W.WBFLGBIS < x.endmtr ) AS OMTR "
                    + " FROM (select distinct A.WBFMANDANT,A.WBFRWKRS,a.wbfrwlfd,a.wbfrwpos,wbffpos,B.WBBSTKNR, "
                    + " (select trim(substr(sddaten,2,20)) from sddat "
                    + " where sdsa = '0267' and substr(sdkey,1,5) = a.wbfmandant||a.wbffschl) as WBFFSCHL,a.wbffschl as SCHL,  "
                    + " (CASE WHEN A.WBFLGVON<1000 THEN 0 ELSE A.WBFLGVON END) AS STARTMTR , "
                    + " lead(a.WBFLGVON,1,DECODE(B.WBBEINDAT,0,0,b.wbbsstklg)) "
                    + " over(partition by A.WBFMANDANT,A.WBFRWKRS,a.wbfrwlfd,a.wbfrwpos  "
                    + " order by A.WBFMANDANT,A.WBFRWKRS,a.wbfrwlfd,a.wbfrwpos,a.wbflgvon,a.wbfversion,a.wbffpos ) as ENDMTR, "
                    + " substr(a.wbffschl,1,1) as SHIFT,B.WBBWARSCH,B.WBBEINDAT,TO_NUMBER(b.wbbwmpos) AS WBBWMPOS,b.wbbwbmlfdnr AS WBBWBMLFDNR, "
                    + " trim(b.wbbart)||'-'||trim(b.wbbfarbe) as article, "
                    + " max(d.VAAALTERNAT)over(partition by b.wbbart) as stru,E.WAFFDDIS*0.01 AS DDIS, "
                    + " DECODE(B.WBBEINDAT,0,0,b.wbbsstklg)*0.001 as PCEMTR,B.WBBWJJJJ,B.WBBWBNR "
                    + " from wbfehl a,wbbst b,vaart d,wastamm e "
                    + " where A.WBFFSCHL<'900' AND A.WBFFSCHL>'500' "
                    + " and a.wbfmandant =b.wbbmandant and a.wbfrwkrs = b.wbbrwkrs "
                    + " and a.wbfrwlfd = b.wbbrwlfd and a.wbfrwpos = b.wbbpos "
                    + " and b.wbbmandant =d.vaamandant  "
                    + " and TRIM(b.Wbbart) = TRIM(d.vaaart) and b.wbbmandant = e.wamandant "
                    + " and b.wbbwerk = e.wawerk and b.wbbkundnr = e.wakundnr and b.wbbabt = e.waabt "
                    + " and b.wbbart = e.waart and b.wbbvar = e.wavar "
                    + " AND TRIM(b.wbbart)||'-'||TRIM(b.wbbfarbe) LIKE '" + CO + "%' "
                    + " AND (TRIM(B.WBBWARSCH) = '" + STAFF + "' OR '" + STAFF + "' = 'AAAA') "
                    + " AND B.WBBEINDAT >= '" + FROMDATE + "' AND B.WBBEINDAT <= '" + ENDDATE + "' "
                    + " AND EXISTS (SELECT '1' FROM VKPOS T WHERE T.VKPKOLL = D.VAAKOLL AND T.VKPART = D.VAAART AND T.VKPSA='  ') "
                    + " ) x ) ORDER BY WBBEINDAT,WBBWJJJJ,WBBWBNR,STARTMTR "
                    ;

                Result = INTEXDBC.Connect2Proxy(orlReadCommand);
                if (Result.Tables.Count > 0)
                {
                    Result.Tables[0].TableName = TableName;
                    byte[] b;
                    string value,str;
                    foreach (DataRow dw in Result.Tables[TableName].Rows)
                    {
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["STRU"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["STRU"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["WBFFSCHL"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["WBFFSCHL"] = value;
                        str = dw["SHIFT"].ToString().Trim();
                        if (str == "5")
                        {
                            dw["SHIFT"] = "甲";
                        }
                        if (str == "6")
                        {
                            dw["SHIFT"] = "乙";
                        }
                        if (str == "7")
                        {
                            dw["SHIFT"] = "丙";
                        }
                    }
                }
                Return.Merge(Result);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return Return;
        }
        
        public DataSet BuildData(DataRow dwUI)
        {
            /*DataSet SResult = new DataSet();
            SETINTEXDB SReturn = new SETINTEXDB(); */
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            try
            {
                setResult = GetRWB18(dwUI);
                setReturn.Merge(setResult);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }

    }
}